package itesm.coler.data;

import itesm.coler.data.DAOUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.StringTokenizer;

import javax.sql.*;

import itesm.coler.models.Problem;
import itesm.coler.models.Session;
import itesm.coler.models.SessionPhase;
import itesm.coler.models.Team;
import itesm.coler.models.User;

public class MySQLSessionDAO implements SessionDAO {
	Connection connection = null;
	
	public MySQLSessionDAO(){
		
		// MySQLSessionDAO implementation of the SessionDAO interface. 
		// This class can contain all MySQL specific code and SQL statements. 
		// 
		// The client is thus shielded from knowing 
		// these implementation details.
	}
    
    public Connection getConnection(){
		if (connection == null)
		    connection = MySQLDAOFactory.createConnection();
		return connection;
	}
	
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
	 public Session findSessionByUserId(String sessionId) throws DAOException {
		 // Implement find a user here using supplied
		 // argument values as search criteria
		 // Return a Transfer Object if found,
		 // return null on error or if not found
        PreparedStatement findSession = null;
        ResultSet results= null;
        Session session = null;
		try{
				  connection = getConnection();
				  if (connection!=null){
					  findSession = connection.prepareStatement("select * from session where sessionid = ?");
					  findSession.setString(1,sessionId);
					  results = findSession.executeQuery();
					  if (results.next()) {
						  session = mapResultSetToObject(results);						  			  	
					  }
				  }
		}catch(SQLException e){
			throw new DAOException(e);
		} finally {
			DAOUtil.close(connection,findSession,results);
		}
		return session;
	 }
	 

    public ArrayList<Session> findAll() throws DAOException {
	    // implement search users here using the
	    // supplied criteria.
	    // Alternatively, implement to return a Collection 
	    // of Transfer Objects.
         PreparedStatement findSession = null;
         ResultSet results= null;
		 ArrayList<Session> sessionList = null;
		 try {
		       connection = getConnection();
		       findSession = connection.prepareStatement("SELECT * FROM session");
		       synchronized(findSession) {
		       // obtain list of sessions	  
		    	  results = findSession.executeQuery();
		       // get rows data{
		    	  sessionList = new ArrayList<Session>();
		         while (results.next()) {
		        	 Session session = mapResultSetToObject(results);		
		        	 sessionList.add(session);	   
		         }
		       }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findSession,results);
		   }
		  return sessionList;
	  }
    
    
    public ArrayList<Session> findAllByProblem(int problemId) throws DAOException {
	    // implement search users here using the
	    // supplied criteria.
	    // Alternatively, implement to return a Collection 
	    // of Transfer Objects.
         PreparedStatement findSessionsByProblem= null;
         ResultSet results= null;
		 ArrayList<Session> sessionList = null;
		 try {
		       connection = getConnection();
		       findSessionsByProblem = connection.prepareStatement("SELECT * FROM session where problemid = ?");
		       synchronized(findSessionsByProblem) {
		    	   findSessionsByProblem.setInt(1,problemId);
		       // obtain list of sessions  
		    	  results = findSessionsByProblem.executeQuery();
		       // get rows data{
		    	  sessionList = new ArrayList<Session>();
		         while (results.next()) {
		        	 Session session = mapResultSetToObject(results);		
		        	 sessionList.add(session);	   
		         }
		       }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findSessionsByProblem,results);
		   }
		  return sessionList;
	  }
    	  


/*
     // The following methods can use
     // MySQLDAOFactory.createConnection() 
     // to get a connection as required
	 public int insertSession(Session session) {
		    // Implement insert user here.
		    // Return 1 if the insert is correct.
		    // or a -1 on error	  

		    PreparedStatement inser = null;
		    int result=-1; //error
			 try { 
				    connection = getConnection();
				   // connection = ds.getConnection();
					insert = connection.prepareStatement("insert into session(username,firstname,middlename,lastname,gender,dateOfBirth, online,userType, active,password) values(?,?,?,?,?,?,?,?,?,?);");
					insert.setString(1, user.getUserName());
					insert.setString(2, user.getFirstName());
					insert.setString(3, user.getMiddleName());
					insert.setString(4, user.getLastName());
					insert.setString(5, user.getGender()+"");
					if(user.getDateOfBirth()!=null){
					insert.setDate(6,DAOUtil.toSqlDate(user.getDateOfBirth()));
					}
					insert.setInt(7, 0); //not online
					insert.setString(8, user.getUserType());
					insert.setInt(9, 1); //active
					insert.setString(10, user.getPassword());
					result = insert.executeUpdate();
					insert.close();
					connection.close();
			} catch (SQLException e) {
				  throw new DAOException(e);
	        } finally {
	              DAOUtil.close(connection, insert);
	        }
			return result;
	 }
	 
	 */
	
		 public Session mapResultSetToObject(ResultSet results) throws SQLException {
			  System.out.println("Voy a mapear el objeto session");
			  Session session = new Session();
			  session.setSessionId(results.getInt("sessionid"));
			  int problemId = results.getInt("problemid");
			  System.out.println(problemId);
			  DAOFactory MySQLFactory = DAOFactory.getDAOFactory(DAOFactory.MySQL);
//	 	  	  ProblemDAO problemDAO = MySQLFactory.getProblemDAO();
//	 	  	  Problem hola = problemDAO.findProblemById(problemId);
			  
			  Connection holi = getConnection();
			  PreparedStatement koko = holi.prepareStatement("select * from problem where problemid = ?");
			  koko.setInt(1, problemId);
			  ResultSet ble = koko.executeQuery();
			  
			  while(ble.next()){  
				  Problem holoProblem = new Problem();
					holoProblem.setProblemId(ble.getInt("problemid"));
					holoProblem.setName(ble.getString("problemname"));
					holoProblem.setDescription(ble.getString("description"));
					holoProblem.setDifficultyLevel(ble.getInt("difficultylevel"));
					holoProblem.setAvgDuration(ble.getInt("durationavg"));
					holoProblem.setUrlPage(ble.getString("pageurl"));
					holoProblem.setActive(ble.getBoolean("active"));
			 	  	  System.out.println("WIiii soy: " + holoProblem.getName() +" y eee: " + holoProblem.getProblemId() + " :3 " + holoProblem.getDescription());
			          session.setProblem(holoProblem);
			  }  
	  /*     if(results.getDate("dateTimeStart") != null){
					java.util.Date utilDate =  results.getDate("dateTimeStart");
				    SimpleDateFormat dia = new SimpleDateFormat("dd");
				    SimpleDateFormat mes = new SimpleDateFormat("MM");
				    SimpleDateFormat anio = new SimpleDateFormat("yyyy");
					user.setBirthDay(dia.format(utilDate));
					user.setBirthMonth(mes.format(utilDate));
					user.setBirthYear(anio.format(utilDate));
				  } */
	   /*       Integer sessionPhaseId = results.getInt("sessionphaseid");
			  DAOFactory MySQLFactory = DAOFactory.getDAOFactory(DAOFactory.MySQL);
	 	  	  SessionPhaseDAO sessionPhaseDAO = MySQLFactory.getSessionPhaseDAO();
	          session.setProblem(sessionPhaseDAO.findSessionPhaseById(sessionPhaseId)); */
	          String userId = results.getString("userid");
	          if (userId!=null) {
	 	  	       UserDAO userDAO = MySQLFactory.getUserDAO();
	               session.setUser(userDAO.findUserByUserId(userId));
	          } else {
	        	  session.setUser(null);
	        	  }
 			  Integer teamId = results.getInt("teamid");
 			  if (teamId!=null){ 
		  	    TeamDAO teamDAO = MySQLFactory.getTeamDAO();
	            session.setTeam(teamDAO.findTeamById(teamId)); 
 			  }else{
 				  session.setTeam(null);
 			  }
			  session.setSessionType(results.getString("sessionType"));
			  session.setTimeLimit(results.getInt("timeLimit"));
			  session.setWaveId(results.getInt("waveId"));
			  session.setActive(results.getBoolean("active"));
			 
			  return session;
		 }
}
